There are 11 hub libraries grouped into 3 clusters. There are 5, 3 and 3 libraries in clusters 1, 2 and 3 respectively.
Every hub library is open for 31 hours a week. No libraries open on Sunday. All libraries are open on Monday, Friday and Saturday. 6 libraries close on Thursday, 4 on Wednesday and 1 on Tuesday.
Note: We’re waiting on data for the central library.
| cluster | library | hours_open |
|---|---|---|
| 1 | Chapeltown | 31 |
| 1 | Firth Park | 31 |
| 1 | Hillsborough | 31 |
| 1 | Parson Cross | 31 |
| 1 | Stocksbridge | 31 |
| 2 | Ecclesall | 31 |
| 2 | Highfield | 31 |
| 2 | Woodseats | 31 |
| 3 | Crystal Peaks | 31 |
| 3 | Darnall | 31 |
| 3 | Manor | 31 |
| day | shifts_cluster1 | shifts_cluster2 | shifts_cluster3 |
|---|---|---|---|
| Monday | 5 | 3 | 3 |
| Tuesday | 5 | 2 | 3 |
| Wednesday | 2 | 3 | 2 |
| Thursday | 3 | 1 | 1 |
| Friday | 5 | 3 | 3 |
| Saturday | 5 | 3 | 3 |
The average FTE scheduled staff hours per hub library is 3.4.
Stocksbridge and Parson Cross libraries have the least FTE (Full Time Equivalent) staff scheduled with an average of 2.8, whilst Firth Park has the most with 4.9.
| library | staff_hours | fte |
|---|---|---|
| Ecclesall | 85:36:15 | 2.3 |
| home | 89:50:00 | 2.4 |
| Darnall | 90:57:30 | 2.5 |
| Stocksbridge | 102:30:00 | 2.8 |
| Manor | 102:56:15 | 2.8 |
| Parson Cross | 102:56:15 | 2.8 |
| Woodseats | 109:36:15 | 3.0 |
| Hillsborough | 111:51:15 | 3.0 |
| Highfield | 119:26:15 | 3.2 |
| Crystal Peaks | 135:00:00 | 3.6 |
| Chapeltown | 149:37:30 | 4.0 |
| Firth Park | 182:36:15 | 4.9 |
Note: Hours are an average over 4 weeks.
The differences in FTE scheduled hours between libraries is not completely explained by the different number of activities at different libraries.
Note: Activities data only available for 4 out of 5 libraries in cluster 1. None available for clusters 2 and 3.
| library | activities_per_week | fte |
|---|---|---|
| Chapeltown | 6 | 4.0 |
| Firth Park | 6 | 4.9 |
| Hillsborough | 6 | 3.0 |
| Stocksbridge | 4 | 2.8 |
---
title: "Libraries dashboard (`r format(Sys.time(), '%d/%m/%y')`)"
output:
flexdashboard::flex_dashboard:
orientation: columns
vertical_layout: fill
source_code: embed
---
```{r setup, include=FALSE}
library(flexdashboard)
library(kableExtra); library(DT)
library(writexl)
source("LibraryFunctions.R")
# Define a theme for the plots in the dashboard
theme_set(theme_minimal())
# Used to create Excel workbook containing output
output_list <- list()
```
```{r global, include=FALSE}
# Load data in 'global' chunk so it can be shared across the dashboard
# Libraries
libraries <- read_xlsx(libraries_data_in_xlsx, "library") %>%
mutate(library_id = as.integer(library_id),
cluster = as.integer(cluster)) %>%
arrange(cluster, library)
# Hub libraries
hubs <- libraries %>%
filter(library_type == "hub")
# Open hours
opening <- read_xlsx(libraries_data_in_xlsx, "opening") %>%
mutate(library_id = as.integer(library_id),
day = factor(day, levels = days_of_work_week),
start = hms::as_hms(start),
end = hms::as_hms(end),
hours_open = hms::as_hms((end - start))) %>%
right_join(select(hubs, library_id, cluster),
by = c("library_id" = "library_id")) %>%
relocate(cluster, .after = library) %>%
arrange(cluster, library)
# Staff
staff <- read_xlsx(libraries_data_in_xlsx, "staff") %>%
mutate(post_id = as.integer(post_id),
cluster = as.integer(cluster),
library_id = as.integer(library_id),
name = str_trim(str_c(first_name, " ", str_replace_na(last_name, ""))),
contract_hours = hms(hours = contract_hours)) %>%
select(post_id, post_number, name, first_name, last_name,
contract_hours, library_id, location, cluster)
# Staff variables
staff_cost <- get_var("staff_cost")
fte_hours <- get_var("fte_hours")
weeks_in_rota_period <- 4
# Schedules
schedule <- read_xlsx(libraries_data_in_xlsx, "schedule") %>%
mutate(post_id = as.integer(post_id),
library_id = as.integer(library_id),
week_no = as.integer(week_no),
day = factor(day, levels = days_of_work_week),
start = hms::as_hms(start),
end = hms::as_hms(end),
lunch = hms::as_hms(lunch),
daily_hours = hms::as_hms((end - start - lunch))) %>%
filter(schedule_type == "current") %>%
right_join(libraries, by = c("library_id" = "library_id")) %>%
left_join(select(staff, post_id, name),
by = c("post_id" = "post_id")) %>%
filter(name != "VACANT") %>%
select(cluster, library_id, library, week_no,
day, start, end, lunch, daily_hours, name, post_id) %>%
arrange(cluster, library, week_no, day, name)
# Activity type
activity_type <- read_xlsx(libraries_data_in_xlsx, "activity_type") %>%
mutate(activity_type_id = as.integer(activity_type_id))
# Activities
activities <- read_xlsx(libraries_data_in_xlsx, "activities") %>%
mutate(activity_type_id = as.integer(activity_type_id),
opening_id = as.integer(opening_id)) %>%
left_join(activity_type, by = c("activity_type_id" = "activity_type_id")) %>%
left_join(opening, by = c("opening_id" = "opening_id")) %>%
relocate(starts_with("activity"), .after = last_col())
```
# Opening hours
## Column {.tabset .tabset-fade data-width="300"}
### Description
```{r library-desc}
# Number of hub libraries
n_hubs <- nrow(hubs)
# Number of hub libraries in each cluster
n_hubs_cluster1 <- nrow(filter(hubs, cluster == 1))
n_hubs_cluster2 <- nrow(filter(hubs, cluster == 2))
n_hubs_cluster3 <- nrow(filter(hubs, cluster == 3))
# Number of hours open a week for each library
opening_summary <- opening %>%
count(library_id, library, cluster,
wt = hours_open/dhours(1), name = "hours_open") %>%
arrange(cluster, library)
#Average hours open a week
n_hours_open <- as.integer(tally(opening_summary, hours_open)/n_hubs)
```
There are `r n_hubs` hub libraries grouped into `r n_clusters` clusters. There are `r n_hubs_cluster1`, `r n_hubs_cluster2` and `r n_hubs_cluster3` libraries in clusters 1, 2 and 3 respectively.
Every hub library is open for `r n_hours_open` hours a week. No libraries open on Sunday. All libraries are open on Monday, Friday and Saturday. 6 libraries close on Thursday, 4 on Wednesday and 1 on Tuesday.
*Note: We're waiting on data for the central library.*
### Hours a week
```{r library-total-hours}
# Present in a nice table
kable(select(opening_summary, cluster, library, hours_open)) %>%
kable_styling("striped", position = "left", full_width = FALSE)
```
### Shift summary
```{r shifts-summary}
shifts_cluster1 <- opening %>%
filter(cluster == 1) %>%
count(day, name = "shifts_cluster1")
shifts_cluster2 <- opening %>%
filter(cluster == 2) %>%
count(day, name = "shifts_cluster2")
shifts_cluster3 <- opening %>%
filter(cluster == 3) %>%
count(day, name = "shifts_cluster3")
shifts_summary <- shifts_cluster1 %>%
left_join(shifts_cluster2, by = "day") %>%
left_join(shifts_cluster3, by = "day")
# Present in a nice table
kable(shifts_summary) %>%
kable_styling("striped", position = "left", full_width = FALSE)
```
## Column {.tabset .tabset-fade}
### Library opening
```{r plot-opening}
opening %>%
arrange(library) %>%
mutate(type = "opening hours") %>%
rename(activity = library) %>%
library_hours_plot("")
```
### List of library opening
```{r list-opening, warning = FALSE}
# Don't need to show the ID fields in the dashboard
opening_list <- opening %>%
select(-ends_with("id"))
# Present in a nice table
datatable(opening_list, rownames = FALSE, filter = "bottom",
options = list(scrollX = TRUE))
```
# Staff schedules
## Column {.tabset .tabset-fade data-width="300"}
### Description
```{r schedules-desc, include=FALSE, warning = FALSE}
# Scheduled staff hours by library
hours_by_library <- schedule %>%
group_by(library_id, library) %>%
summarise(staff_hours = hms::as_hms(sum(daily_hours)/weeks_in_rota_period)) %>%
ungroup() %>%
mutate(fte = round(staff_hours/dhours(fte_hours), 1)) %>%
filter(!is.na(staff_hours)) %>%
arrange(staff_hours)
# Number of FTE hub library staff scheduled
n_hub_scheduled_fte <- round((sum(hours_by_library$staff_hours)/dhours(1))/fte_hours, 1)
# Average number of FTE staff scheduled per hub library
n_ave_hub_scheduled_fte <- round(n_hub_scheduled_fte/n_hubs, 1)
```
The average FTE scheduled staff hours per hub library is `r n_ave_hub_scheduled_fte`.
Stocksbridge and Parson Cross libraries have the least FTE (Full Time Equivalent) staff scheduled with an average of 2.8, whilst Firth Park has the most with 4.9.
### By library
```{r hours-by-library, warning = FALSE}
# Present in a nice table
select(hours_by_library, library, staff_hours, fte) %>%
kable() %>%
kable_styling("striped", position = "left", full_width = FALSE)
```
*Note: Hours are an average over 4 weeks.*
## Column {.tabset .tabset-fade}
### Staff hours by library & week
```{r plot-hours-by-library-week, warning = FALSE}
# Scheduled staff hours by library & week
hours_by_library_week <- schedule %>%
rename(week = week_no) %>%
group_by(library, week) %>%
summarise(staff_hours = hms::as_hms(sum(daily_hours))) %>%
mutate(fte = round(staff_hours/dhours(fte_hours), 1),
week = factor(week)) %>%
filter(!is.na(staff_hours)) %>%
arrange(library, week)
# Plot of staff hours by library and week
ggplot(data=hours_by_library_week,
aes(x=library, y=fte, fill=week)) +
geom_bar(stat="identity", position=position_dodge()) +
theme(axis.text.x = element_text(angle = 315, hjust=0.1),
plot.margin = unit(c(5.5,12,5.5,5.5), "pt"),
legend.position = c(0.8, 0.8),
legend.key.size = unit(0.4, 'cm'))
```
# Activities
## Column {.tabset .tabset-fade data-width="300"}
### Description
The differences in FTE scheduled hours between libraries is not completely explained by the different number of activities at different libraries.
*Note: Activities data only available for 4 out of 5 libraries in cluster 1. None available for clusters 2 and 3.*
### By library
```{r activities-by-library, warning = FALSE}
# Number of activities per library (per week)
activities_by_library <- activities %>%
count(library_id, library, name = "activities_per_week") %>%
mutate(activities_per_week = ifelse(is.na(library), 0, activities_per_week)) %>%
select(-library) %>%
left_join(libraries, by = c("library_id" = "library_id")) %>%
arrange(desc(activities_per_week), library)
# Add average scheduled FTE column
activities_by_library %<>%
left_join(select(hours_by_library, -library),
by = c("library_id" = "library_id"))
# Present in a nice table
select(activities_by_library, library, activities_per_week, fte) %>%
kable() %>%
kable_styling("striped", position = "left", full_width = FALSE)
```
## Column {.tabset .tabset-fade}
### Activities & scheduled staff hours by library
```{r plot-activities-by-library, warning = FALSE}
# Define the two colours we're using to help compare activities & FTE hours
activities_colour = "blue4"
fte_colour = "olivedrab4"
# Order the libraries how we want them to be plotted
activities_by_library %<>%
arrange(-activities_per_week, -fte) %>%
mutate(library = factor(library, levels=library))
# Plot of activities and staff hours by library
ggplot(data=activities_by_library,
aes(x=library)) +
geom_point(aes(y=activities_per_week), colour=activities_colour, size=3) +
geom_point(aes(y=fte), colour=fte_colour, size=3) +
scale_x_discrete(name = "library") +
scale_y_continuous(name = "activities per week",
sec.axis = sec_axis(trans=~.*1,
name="FTE scheduled hours")) +
expand_limits(y = 0) +
theme(axis.title.y = element_text(colour = activities_colour),
axis.line.y = element_line(color = activities_colour),
axis.ticks.y = element_line(color = activities_colour),
axis.title.y.right = element_text(colour = fte_colour),
axis.line.y.right = element_line(color = fte_colour),
axis.ticks.y.right = element_line(color = fte_colour),
axis.text.x = element_text(angle = 315, hjust=0.1),
plot.margin = unit(c(5.5,12,5.5,5.5), "pt"))
```
### List of activities
```{r activities-list, warning = FALSE}
# Don't need to include ID fields in the dashboard
activities_list <- activities %>%
select(-ends_with("_id"))
# Present in a nice table
datatable(activities_list, rownames = FALSE, filter = "bottom",
options = list(scrollX = TRUE))
```